package com.bdqn.oa.dao.impl;

import com.bdqn.oa.dao.EmployeeDao;
import com.bdqn.oa.entity.Employee;
import com.bdqn.oa.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class EmployeeDaoImpl implements EmployeeDao {
    QueryRunner queryRunner = new QueryRunner();
    /**
     * 通过部门ID查询部门的员工
     *
     * @param deptid
     * @return
     */
    @Override
    public List<Employee> listEmployeebyDeptid(Integer deptid) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag  from employee where dept_id = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Employee>(Employee.class),deptid);
    }

    /**
     * 通过直属领导ID查询员工
     *
     * @param leadid
     * @return
     */
    @Override
    public List<Employee> listEmployeebyLeadid(Integer leadid) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee where lead_id = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Employee>(Employee.class),leadid);
    }

    /**
     * 通过工号查询员工no
     *
     * @param no
     * @return
     */
    @Override
    public Employee getEmployeeByNo(String no) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee where no = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Employee>(Employee.class),no);
    }

    /**
     * 通过员工姓名查询员工的信息
     *
     * @param name
     * @return
     */
    @Override
    public List<Employee> listEmployeebyName(String name) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee where name = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Employee>(Employee.class),name);
    }

    /**
     * 通过身份证查询员工信息
     *
     * @param idcard
     * @return
     */
    @Override
    public Employee getEmployeeByIdcardNo(String idcard) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from  employee  where idcard_no = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Employee>(Employee.class),idcard);
    }

    /**
     * 通过手机号查询员工
     *
     * @param tel
     * @return
     */
    @Override
    public Employee getEmployeeByTel(String tel) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee where tel = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Employee>(Employee.class),tel);
    }

    /**
     * 通过工号和密码查询员工
     *
     * @param employee
     * @return
     */
    @Override
    public Employee getEmloyeeByNoAndPassWord(Employee employee) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee  where no = ? and password = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Employee>(Employee.class),employee.getNo(),employee.getPassword());
    }

    /**
     * 修改员工的密码（Emloyeee 赋值id  和 password）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeePassword(Employee employee) throws SQLException {
        String sql = "update employee set password = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getPassword(),employee.getId());
    }

    /**
     * 修改员工的电话（Emloyeee 赋值id  和 tel）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeTel(Employee employee) throws SQLException {
        String sql = "update employee set tel = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getTel(),employee.getId());
    }

    /**
     * 修改员工的邮箱（Emloyeee 赋值id  和 emaile）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeEmail(Employee employee) throws SQLException {
        String sql = "update employee set email = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getEmail(),employee.getId());
    }

    /**
     * 修改员工的姓名（Emloyeee 赋值id  和 name）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeName(Employee employee) throws SQLException {
        String sql = "update employee set name = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getName(),employee.getId());
    }

    /**
     * 修改员工的身份证（Emloyeee 赋值id  和 IdCardNo）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeIdCardNo(Employee employee) throws SQLException {
        String sql = "update employee set idcard_no = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getIdcardNo(),employee.getId());
    }

    /**
     * 转岗位（ Emloyeee 赋值id修改postid deptid leadid）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeePostId(Employee employee) throws SQLException {
        String sql = "update employee set post_id = ?,dept_id = ?,lead_id = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getPostId(),employee.getDeptId(),employee.getLeadId(),employee.getId());
    }

    /**
     * 修改角色roleid（ Emloyeee 赋值id修改roleid）
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeRoleId(Employee employee) throws SQLException {
        String sql = "update employee set role_id = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getRoleId(),employee.getId());
    }

    /**
     * 修改删除标记（（ Emloyeee 赋值id修改roleid））
     *
     * @param employee
     * @return
     */
    @Override
    public int updateEmployeeDelFlag(Employee employee) throws SQLException {
        String sql = "update employee set del_flag = ? where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,employee.getDelFlag(),employee.getId());
    }

    /**
     * 通过删除标记查询员工
     *
     * @param employee
     * @return
     * @throws SQLException
     */
    @Override
    public List<Employee> listEmployeebyDelFlag(Employee employee) throws SQLException {
        String sql = "select `id`, `no`,`name`,`idcard_no` as idcardno,`tel`,`email`,`password`,`post_id` as postid,`dept_id` as deptid ,`lead_id` as leadid,`role_id` as roleid,`del_flag` as delflag from employee where del_flag = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Employee>(Employee.class),employee.getDelFlag());
    }
}
